/**
 * 
 */
package com.testbank.practise;

import java.util.Collections;
import java.util.Vector;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.testbank.QSet;
import com.testbank.practise.sqltables.PracticeAttemptsTable;
import com.testbank.practise.sqltables.PracticeStateTable;
import com.testbank.practise.sqltables.QuestionSetsTable;
import com.testbank.practise.sqltables.TestAttemptsTable;
import com.testbank.practise.sqltables.TestScoresTable;

/**
 * @author Guo Wei
 *
 */
/**
 * @author Guo Wei
 *
 */
/**
 * @author Guo Wei
 *
 */
public class DBManager {
	private final Context _mCtx;
	private static final String TAG = "DBManager";
	private DatabaseHelper _dbHelper;
	private SQLiteDatabase _db;

	private static final String DATABASE_NAME = "drivingDb";
	private static final int DATABASE_VERSION = 2;

	private final int TEST_ATTEMPTS_TO_KEEP = 5;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		//opens the SQLDatabase
		DatabaseHelper (Context context){
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		//initializes the SQLDatabase Tables
		@Override
		public void onCreate(SQLiteDatabase sqlDb) {
			sqlDb.execSQL(QuestionSetsTable.CREATE);
			sqlDb.execSQL(TestAttemptsTable.CREATE);
			sqlDb.execSQL(TestScoresTable.CREATE);
			sqlDb.execSQL(PracticeAttemptsTable.CREATE);
			sqlDb.execSQL(PracticeStateTable.CREATE);
		}

		//Action to perform after opening the database and tables
		@Override
		public void onOpen(SQLiteDatabase db) {

		}

		//Delete all tables if version updated
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + QuestionSetsTable.TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + TestAttemptsTable.TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + TestScoresTable.TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + PracticeAttemptsTable.TABLE_NAME);
			db.execSQL("DROP TABLE IF EXISTS " + PracticeStateTable.TABLE_NAME);
			onCreate(db);
		}
	}


	public DBManager (Context context) {
		_mCtx = context;
	}

	/**
	 * Open the notes database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException if the database could be neither opened or created
	 */
	public DBManager open() throws SQLException {
		_dbHelper = new DatabaseHelper(_mCtx);
		_db = _dbHelper.getWritableDatabase();
		return this;
	}

	//closes the database
	public void close() {
		_db.close();
	}

	
	/** insert 1 question set
	 * @param question
	 * @param image
	 * @param op1
	 * @param op2
	 * @param op3
	 * @param feedback
	 * @param category
	 * @param book
	 * @return the question id of the inserted question set
	 */
	public long insertQSet (String question, String image, String op1, String op2, String op3, String feedback, int category, int book) {
		ContentValues newQSet = QuestionSetsTable.GetCVRow(question, image, op1, op2, op3, feedback, category, book);
		return _db.insert(QuestionSetsTable.TABLE_NAME, null, newQSet);
	}

	public Cursor getQSet(int qId) {
		String whereClause = QuestionSetsTable.KEY_QID + "=" + qId;
		Cursor tempCursor = _db.query(true, QuestionSetsTable.TABLE_NAME, null, whereClause, null, null, null, null, null);

		if (tempCursor != null && tempCursor.getCount() != 0) 
			tempCursor.moveToFirst();
		return tempCursor;
	}

	/** Gets the previous test progress
	 * @return the progress in int if exists, else returns -1
	 */
	public int getIncompleteTestProgress() {
		//default no previous progress
		int testProgress = -1;
		//retrieve previous test
		Cursor previousTest = _db.query(true, TestScoresTable.TABLE_NAME, null, null, null, null, null, TestScoresTable.KEY_ATTEMPT_NUM + " DESC", "1");
		//there is a previous test
		if (previousTest != null && previousTest.getCount() != 0) {
			previousTest.moveToFirst();

			//check if test is completed
			int columnIndex = previousTest.getColumnIndex(TestScoresTable.KEY_COMPLETED);
			int testCompleted = previousTest.getInt(columnIndex);
			//1 = true, 0 = false
			//test not completed
			if (testCompleted == 0) {
				Log.w("Previous Test Check", "Test not completed");
				//return the progress
				columnIndex = previousTest.getColumnIndex(TestScoresTable.KEY_STOPPED_AT);
				testProgress = previousTest.getInt(columnIndex);

				//clean up cursor
				previousTest.close();
			}
		}
		return testProgress;
	}

	/** Retrieves the QBook and sets up the other tables in preparation
	 * @param bookNum book number to retrieve
	 * @return question sets in a vector
	 */
	public Vector<QSet> getNewQBook(int bookNum){
		//get previous attempt number or a new number
		long previousAttemptNum = getPreviousTestAttemptNum();
		long currentAttemptNum = previousAttemptNum + 1;

		//check if previous test exist
		int previousTestProgress = getIncompleteTestProgress();
		//previous test exists
		if (previousTestProgress != -1) {
			//reset score table
			_db.delete(TestScoresTable.TABLE_NAME, TestScoresTable.KEY_ATTEMPT_NUM + "=" + previousAttemptNum, null);
			//reset old book
			_db.delete(TestAttemptsTable.TABLE_NAME, TestAttemptsTable.KEY_ATTEMPT_NUM + "=" + previousAttemptNum , null);
			//set to previous attempt number
			currentAttemptNum = previousAttemptNum;
		}

		//set new scoreTable row (default id is used as attempt number)
		_db.insert(TestScoresTable.TABLE_NAME, null, TestScoresTable.GetCVRow(currentAttemptNum, 0, 0, 0, 1));

		//retrieve book questions from QSet
		String bookNumWhereClause = QuestionSetsTable.KEY_BOOK + "=" + bookNum;
		Cursor bookCursor = _db.query(true, QuestionSetsTable.TABLE_NAME, null, bookNumWhereClause, null, null, null, null, null);
		if (bookCursor != null && bookCursor.getCount() != 0)
			bookCursor.moveToFirst();

		//convert into QSet using actual QID as the local ID first
		Vector<QSet> book = new Vector<QSet>();
		for (int i = 0; i < bookCursor.getCount(); i++) {
			book.add(convertToQSet(null, bookCursor));
			bookCursor.moveToNext();
		}
		//clean up book cursor
		bookCursor.close();

		//shuffle the question's arrangement
		Collections.shuffle(book);

		//store into test attempt table and record the new local id
		for (int i = 0; i < book.size(); i++) {
			QSet currentQ = book.get(i);
			long localId = _db.insert(TestAttemptsTable.TABLE_NAME, null, TestAttemptsTable.GetCVRow(currentAttemptNum, currentQ.getID(), null, 0));
			currentQ.setID(localId);
		}

		return book;
	}

	/** Retrieves the unfinished book
	 * @param attemptNum attempt number of the unfinished book
	 * @return question sets in a vector
	 */
	public Vector<QSet> getOldQBook(long attemptNum) {
		Vector<QSet> oldBook = new Vector<QSet>();
		long previousAttemptNum = getPreviousTestAttemptNum();

		//retreive pointer to all questions with correct attempt number in SQL format
		String whereClause = TestAttemptsTable.KEY_ATTEMPT_NUM + "=" + previousAttemptNum;
		Cursor localCursor = _db.query(true, TestAttemptsTable.TABLE_NAME, null, whereClause, null, null, null, null, null);
		if (localCursor != null && localCursor.getCount() != 0)
			localCursor.moveToFirst();

		//convert SQL format into QSet class
		for (int i = 0; i < localCursor.getCount(); i++) {
			//get question id 
			long questionId = localCursor.getLong(TestAttemptsTable.KEY_Q_ID_INDEX);
			//get actual question in SQL format
			String qIdWhereClause = QuestionSetsTable.KEY_QID + "=" + questionId;
			Cursor questionCursor = _db.query(true, QuestionSetsTable.TABLE_NAME, null, qIdWhereClause, null, null, null, null, null);
			if (questionCursor != null && questionCursor.getCount() > 0)
				questionCursor.moveToFirst();

			//convert to QSet class and add to vector
			oldBook.add(convertToQSet(localCursor, questionCursor));
			//next local question
			localCursor.moveToNext();

			//clean up question cursor
			questionCursor.close();
		}

		//clean up local cursor
		localCursor.close();

		return oldBook;
	}

	/** Updates the database and does necessary changes to reflect test completed
	 * @param testScore score of the test that was just completed
	 */
	public void testCompleted(int testScore) {
		//set testScoresTable row to be completed and record the score
		long previousTestAttemptNum = getPreviousTestAttemptNum();
		ContentValues updateScoreRow = TestScoresTable.GetCVRow(previousTestAttemptNum, testScore, 0, 1, 1);
		_db.update(TestScoresTable.TABLE_NAME, updateScoreRow, TestScoresTable.KEY_ATTEMPT_NUM + "=" + previousTestAttemptNum, null);

		//delete oldest attempt on score table and test attempt if exceed limit
		long oldestTestAttemptNum = getOldestTestAttemptNum();
		if (previousTestAttemptNum - oldestTestAttemptNum >= TEST_ATTEMPTS_TO_KEEP) {
			_db.delete(TestScoresTable.TABLE_NAME, TestScoresTable.KEY_ATTEMPT_NUM + "=" + oldestTestAttemptNum, null);
			_db.delete(TestAttemptsTable.TABLE_NAME, TestAttemptsTable.KEY_ATTEMPT_NUM + "=" + oldestTestAttemptNum, null);
		}
	}

	/** Gets the oldest test attempt number.
	 * @return returns the oldest test attempt number, else returns 0
	 */
	private long getOldestTestAttemptNum() { 
		//get oldest test cursor
		Cursor oldestTest = _db.query(true,  TestScoresTable.TABLE_NAME,  null, null, null, null, null, TestScoresTable.KEY_ATTEMPT_NUM + " ASC", "1");
		if (oldestTest != null && oldestTest.getCount() != 0)
			oldestTest.moveToFirst();
		//if does not exist
		else {
			oldestTest.close();
			return 0;
		}

		//retrieve oldest attempt number
		long oldestTestAttemptNum = oldestTest.getLong(TestScoresTable.KEY_ATTEMPT_NUM_INDEX);
		//clean up cursor
		oldestTest.close();

		return oldestTestAttemptNum;
	}

	/** Gets the previous test attempt number. 
	 * @return returns the previous test attempt number, else returns 0 
	 */
	public long getPreviousTestAttemptNum() {
		//get previous test cursor
		Cursor previousTest = _db.query(true, TestScoresTable.TABLE_NAME, null, null, null, null, null, TestScoresTable.KEY_ATTEMPT_NUM + " DESC", "1");
		if (previousTest != null && previousTest.getCount() != 0) 
			previousTest.moveToFirst();
		//if does not exist
		else {
			previousTest.close();
			return 0;
		}

		//retrieve previous attempt number
		long previousTestAttemptNumber = previousTest.getLong(TestScoresTable.KEY_ATTEMPT_NUM_INDEX);

		//clean up cursor
		previousTest.close();

		return previousTestAttemptNumber;
	}

	/** Converts options arrangement String into a vector
	 * @param opArr String of options arrangement delimited by ",". null if no arrangement
	 * @return
	 */
	private Vector<Integer> convertOpArrToVector(String opArr) {
		Vector<Integer> arrangement = new Vector<Integer>();
		//if no arrangement
		if (opArr == null) {
			for (int i = 0; i < 3; i++)
				arrangement.add(i+1);
		}
		else {
			String[] tempArr = opArr.split(",");
			for (int i = 0; i < tempArr.length; i++) 
				arrangement.add(Integer.parseInt(tempArr[i]));
		}

		return arrangement;
	}

	/** Converts options into a vector
	 * @param op1
	 * @param op2
	 * @param op3
	 * @return 
	 */
	private Vector<String> convertOpToVector(String op1, String op2, String op3) {
		Vector<String> options = new Vector<String>();
		options.add(op1);
		options.add(op2);
		options.add(op3);
		return options;
	}

	/** Takes in 2 cursors and parse the data into a QSet accordingly. will check for image data
	 * @param localCursor cursor pointing to the questions stored in the attempts table
	 * @param questionCursor cursor pointing to the actual question in the QSets table
	 * @return a QSet with the respective data loaded
	 */
	private QSet convertToQSet(Cursor localCursor, Cursor questionCursor) {
		String opArrangement = null;
		//store actual question id if localCursor is not given
		long localId = questionCursor.getLong(QuestionSetsTable.KEY_QID_INDEX);

		if (localCursor != null) {
			opArrangement = localCursor.getString(TestAttemptsTable.KEY_OP_ARR_INDEX);
			localId = localCursor.getLong(TestAttemptsTable.KEY_ID_INDEX);
		}

		//check for image
		String image = questionCursor.getString(QuestionSetsTable.KEY_IMAGE_INDEX);
		//no image
		if (image == null) {		
			return new QSet(questionCursor.getString(QuestionSetsTable.KEY_QUESTION_INDEX),
					convertOpToVector(questionCursor.getString(QuestionSetsTable.KEY_OP1_INDEX), questionCursor.getString(QuestionSetsTable.KEY_OP2_INDEX), questionCursor.getString(QuestionSetsTable.KEY_OP3_INDEX)),
					convertOpArrToVector(opArrangement),
					questionCursor.getString(QuestionSetsTable.KEY_FEEDBACK_INDEX),
					localId);
		}
		else {
			/*	return new QSet(questionCursor.getString(QuestionSetsTable.KEY_QUESTION_INDEX),
							convertOpToVector(questionCursor.getString(QuestionSetsTable.KEY_OP1_INDEX), questionCursor.getString(QuestionSetsTable.KEY_OP2_INDEX), questionCursor.getString(QuestionSetsTable.KEY_OP3_INDEX)),
							convertOppArrToVector(opArrangement),
							questionCursor.getString(QuestionSetsTable.KEY_FEEDBACK_INDEX),
							localCursor.getInt(TestAttemptsTable.KEY_ID_INDEX),
							localId);*/
			return null;
		}
	}
}
